package com.onemore.task.model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.voovan.db.JdbcOperate;
import com.onemore.task.JdbcGlobal;
import com.onemore.task.OneConfig;

public class ModelOneWms2Erp extends JdbcGlobal{
	final static private String fields[] = new String[] {"SyncBillId","BILLOUTDATE","QTYIN","SL"};

	static public boolean isNotExist()
	{
		return validateTableExist("ONE_WMS2ERP");
	}
	static public String getFrom()
	{
		String filterDate = "";
		String cangku = "'09001','09002'";
		String ycdfilter = "'1018','2001'";
		String starDate = null;//req.getParameter("starDate");
		String endDate = null;//req.getParameter("endDate");
		Properties properties= OneConfig.getProperties();
		if(properties != null) {
			String str = OneConfig.getProperties().getProperty("cangku");
			if(str != null) {
				cangku = str;
			}

			if(starDate == null) {
				str = OneConfig.getProperties().getProperty("starDate");
				if(str != null) {
					starDate = str;
				}
			}
			str = OneConfig.getProperties().getProperty("ycdfilter");
			if(str != null) {
				ycdfilter = str;
			}
		}
		if(starDate != null && starDate.length() > 0) {
			filterDate = " billdate>='"+starDate+"' AND";
		}else {
			starDate = "2018";
		}
		if(endDate != null && endDate.length() > 0) {
			filterDate += " billdate<='"+endDate+"' AND";
		}
		
		List<Map<String, Object>> listSpjhd = new ArrayList<Map<String, Object>>();
		return "SELECT wms.BillId, DJBH, MAX(billdate) AS CDate, MAX(YDJH) AS YDJH, sum(QTYIN) AS QTYIN, SUM (SL) AS SL FROM ONE_WMS wms LEFT JOIN (select DJBH,YDJH,RQ,SL from SPJHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from SPTHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from PFXHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from PFTHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from SDPHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select YDJH AS DJBH,DJBH AS YDJH,RQ,SL from SDTHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from QDDBD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select YDJH AS DJBH,DJBH AS YDJH,RQ,SL from QDTHD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from CKTZD where DM2 IN ("+cangku+") AND RQ>='"+starDate+"' union all select DJBH,YDJH,RQ,SL from SPYCD where (DM2 IN ("+cangku+") OR DM1 IN ("+cangku+")) AND RQ>='"+starDate+"'  UNION ALL select DJBH,YDJH,RQ,SL_2 as SL from YSEND where RQ>='"+starDate+"' AND DM1 IN ("+cangku+") AND NOT (dm1 IN ("+ycdfilter+") AND dm2 IN ("+ycdfilter+"))) erp ON erp.YDJH = wms.BillId WHERE "+filterDate+" ( erp.DJBH IS NULL OR erp.SL != wms.QTYIN OR erp.YDJH IS NULL ) GROUP BY wms.BillId, DJBH";
		
	}
	static public List<Map<String, Object>> getList(String starDate,String endDate) throws SQLException
    {
		String filterDate = "";
		if(starDate != null && starDate.length() > 0) {
			filterDate = " AND CDate>='"+starDate+"'";
		}
		if(endDate != null && endDate.length() > 0) {
			filterDate += " AND CDate<='"+endDate+"'";
		}
		String sql = "SELECT * FROM ONE_WMS2ERP where 1=1"+filterDate;
    	List<Map<String, Object>> manyMaps = null;
		manyMaps = getJdbcOperate().queryMapList(sql);
		return manyMaps;
    }

	static public void importTable() throws SQLException
	{
		dropTable();
		createTable();
	}
	static public void dropTable() throws SQLException
	{
		String sql = "drop table ONE_WMS2ERP";
    	getJdbcOperate().update(sql);
	}
    static public void createTable() throws SQLException
    {
    	String sql = "select * into ONE_WMS2ERP FROM ("+getFrom()+") t";
    	getJdbcOperate().update(sql);
    }
}
